package com.servlet;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.model.Student;
import com.model.User;
import com.util.JdbcUtil;

/**
 * Servlet implementation class StudentServlet
 */
@WebServlet("/student")
public class StudentServlet extends BaseServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public StudentServlet() {
        super();
        // TODO Auto-generated constructor stub
    }
	
    protected void addStudent(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException{
    	request.getRequestDispatcher("addStudent.jsp").forward(request, response);
    }
    
	protected void update(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException{
		String sid = request.getParameter("sid");
		String sname = request.getParameter("sname");
		String sex = request.getParameter("sex");
		String age = request.getParameter("age");
		String school = request.getParameter("school");
		String grade = request.getParameter("grade");
		String major = request.getParameter("major");
		String phone = request.getParameter("phone");
		
		String sql="update student set sname = ?,age = ?,sex = ?,phone = ?,school = ?,grade = ?,major = ? where sid = ?";
		JdbcUtil jdbc = new JdbcUtil();
		try {
			jdbc.updatePreparedStatement(sql, sname,age,sex,phone,school,grade,major,sid);
			//查询数据库中学生列表 
			request.getRequestDispatcher("student?action=query").forward(request, response);
		} catch (Exception e) {
			e.printStackTrace();
			request.setAttribute("mess", "修改失败，请稍后在试");
			request.getRequestDispatcher("error.jsp").forward(request, response);
		}
	}
	
	protected void toupdate(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException{
		String sid = request.getParameter("sid");
		String sql="select * from student where sid =? ";
		JdbcUtil jdbc = new JdbcUtil();
		List<Student> stus = jdbc.queryPreparedStatement(sql, Student.class, sid);
		request.setAttribute("stu", stus.get(0));
		request.getRequestDispatcher("updateStudent.jsp").forward(request, response);
		
	}
	
	protected void delete(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException{
		String sid = request.getParameter("sid");
		JdbcUtil jdbc = new JdbcUtil();
		String sql="delete from student where sid=?";
		try {
			jdbc.updatePreparedStatement(sql,sid);
			//查询数据库中学生列表 
			request.getRequestDispatcher("student?action=query").forward(request, response);
		
		} catch (Exception e) {
			e.printStackTrace();
			request.setAttribute("mess", "删除失败，请稍后在试");
			request.getRequestDispatcher("error.jsp").forward(request, response);
		}
	}
	
	protected void query(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException{
		Integer pagesize=5;
		Integer cp = Integer.parseInt(request.getParameter("cp")==null?"1":request.getParameter("cp"));
		
		JdbcUtil jdbc = new JdbcUtil();
		
		String sid = request.getParameter("sid");
		
		StringBuffer sb = new StringBuffer();
		sb.append("select sid, sname, age, sex, phone, school, grade, major from (");
		sb.append("select rownum as rn,t.* from (");
		
		/******最内层sql******/
		StringBuffer sql = new StringBuffer();
		sql.append("select * from student where 1=1 ");
		if(sid!=null && !sid.equals("")){
			sql.append(" and sid like '%"+sid+"%'");
		}
		sql.append("order by sid asc");
		/******最内层sql******/
		
		sb.append(sql);
		sb.append(") t where rownum<="+(cp*pagesize)+") t2 where t2.rn>"+(cp-1)*pagesize);
		
		List<Student> stus = jdbc.queryPreparedStatement(sb.toString(), Student.class);
		
		
		List<Student> temp = jdbc.queryPreparedStatement(sql.toString(), Student.class);
		
		//总条数
		Integer count = temp.size();
		//总页数
		Integer total = count%pagesize==0?count/pagesize:count/pagesize+1;
		
		request.setAttribute("stus", stus);
		request.setAttribute("cp", cp);
		request.setAttribute("count", count);
		request.setAttribute("total", total);
		request.getRequestDispatcher("student.jsp").forward(request, response);
	}
	
	
	protected void add(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException{
		
		String sid = request.getParameter("sid");
		String sname = request.getParameter("sname");
		String sex = request.getParameter("sex");
		String age = request.getParameter("age");
		String school = request.getParameter("school");
		String grade = request.getParameter("grade");
		String major = request.getParameter("major");
		String phone = request.getParameter("phone");
		
		JdbcUtil jdbc = new JdbcUtil();
		String sql="insert into student(sid, sname, age, sex, phone, school, grade, major) values (?, ?, ?, ?, ?, ?, ?, ?)";
		
		Object[] param = new Object[]{sid,sname,Integer.parseInt(age),sex,phone,school,grade,major};
		
		try {
			jdbc.updatePreparedStatement(sql, param);
			
			//查询数据库中学生列表 
			request.getRequestDispatcher("student?action=query").forward(request, response);
		} catch (Exception e) {
			e.printStackTrace();
			request.setAttribute("mess", "新增学生失败，请检查录入学号是否重复！！");
			request.getRequestDispatcher("error.jsp").forward(request, response);
		}
	}

}
